package com.cf.dao.impl;

import com.cf.dao.CommentDao;
import com.cf.dao.db.BaseDao;
import com.cf.dao.entity.Comment;
import com.cf.dao.entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class CommentDaoImpl extends BaseDao implements CommentDao {
    //用户评论总数
    @Override
    public List<User> selectUserCommentCount() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT account,name,id,avatar,commentall FROM user";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(User.class));
        }
    }
    //查询作品评论
    @Override
    public List<Comment> selectCommentByWorkId(int workId) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());

        // 更新的 SQL 查询，使用 JOIN 查询 comment 和 user 表
        String sql = "SELECT c.id AS comment_id, c.comment, c.date, c.worksid, c.userid, u.avatar, u.name " +
                "FROM comment c " +
                "JOIN user u ON c.userid = u.id " +
                "WHERE c.worksid = ?";

        try (Connection conn = getDataSource().getConnection()) {
            // 使用 ResultSetHandler 来处理结果并映射到 Comment 对象（可以自定义映射）
            return qr.query(conn, sql, new BeanListHandler<>(Comment.class), workId);
        }
    }
    //插入评论
    @Override
    public void insertComment(int userid, String content, int worksid, String date) throws SQLException {
        // 使用单一数据源对象，避免每次调用都创建连接
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "INSERT INTO comment (comment, date, worksid, userid) VALUES (?, ?, ?, ?)";

        try (Connection conn = getDataSource().getConnection()) {
            // 如果需要事务处理，可以在此开始事务
            conn.setAutoCommit(false);

            try {
                // 执行插入操作
                qr.update(conn, sql, content, date, worksid, userid);

                // 提交事务
                conn.commit();
            } catch (SQLException e) {
                // 如果出现异常，回滚事务
                conn.rollback();
                throw e; // 重新抛出异常
            } finally {
                // 还原自动提交模式
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            // 记录异常信息
            throw new SQLException("插入评论时发生错误", e);
        }
    }
    //更新评论总数
    @Override
    public void updateCommentCount(int userid) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "UPDATE user SET commentall = commentall + 1 WHERE id = ?";
        qr.update(sql, userid);
    }
    //删除评论
    @Override
    public void updateScore(int worksid, String score ,int userid) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "UPDATE score SET score = ?,userid=? WHERE id = ? ";
        qr.update(sql, score, userid,worksid);
    }

}




